﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Hfm.Common.Entity.Customer;
using Hfm.Common.Constants.Customer;

namespace Hfm.Core.DataAccess.Customer
{
    public class CustomerData
    {
        protected readonly CoreContext Context;
        public CustomerData()
        {

        }

        public CustomerData(CoreContext coreContext)
        {
            Context = coreContext;
        }
        /// <summary>
        /// 查询所有的Customer
        /// </summary>
        /// <returns></returns>
        public virtual IList<CustomerEntity> GetAllCustomer(int? skip = null, int? take = null)
        {
            if ((skip == null) ^ (take == null))
                throw new ArgumentException("Skip and take parameter must be provided together or left null.");

            var query = from e in Context.CustomerEntities
                        select e;

            if (skip != null && take != null)
            {
                query = query.Skip(skip ?? 0).Take(take ?? int.MaxValue);
            }

            return query.ToList();
        }

        /// <summary>
        /// 根据Id查询Customer
        /// </summary>
        /// <param name="CustomerId"></param>
        /// <returns></returns>
        public virtual CustomerEntity GetCustomerById(int CustomerId)
        {
            var query = from c in Context.CustomerEntities
                where c.Id == CustomerId
                select c;
            return query.SingleOrDefault();
        }
        /// <summary>
        /// 根据Id查询Customer
        /// </summary>
        /// <param name="CustomerId"></param>
        /// <returns></returns>
        public virtual NewCustomerEntity GetNewCustomerById(int CustomerId)
        {
            var query = from c in Context.CustomerEntities
                join i in Context.IndustryEntities
                    on c.IndustryId equals i.Id into custindus
                from ci in custindus.DefaultIfEmpty()
                join e in Context.EmployeeEntities
                    on c.Employee equals e.Id into custemp
                from ce in custemp.DefaultIfEmpty()
                join co in Context.CountryEntities
                    on c.CountryId equals co.CountryId into custcount
                from cco in custcount.DefaultIfEmpty()
                where c.Id ==CustomerId
                select new NewCustomerEntity
                {
                    Id = c == null ? 0 : c.Id,
                    Address = c == null ? string.Empty : c.Address,
                    CountryId = c == null ? 0 : c.CountryId,
                    Country = cco == null ? string.Empty : cco.CountryName,
                    CustomerCode = c == null ? string.Empty : c.CustomerCode,
                    CustomerName = c == null ? string.Empty : c.CustomerName,
                    Email = c == null ? string.Empty : c.Email,
                    Employee = c == null ? 0 : c.Employee,
                    EmployeeName = ce == null ? string.Empty : ce.Name,
                    FaxNumber = c == null ? string.Empty : c.FaxNumber,
                    Status = c == null ? 0 : c.Status,
                    IndustryId = c == null ? 0 : c.IndustryId,
                    Industry = ci == null ? string.Empty : ci.Name,
                    TelNumber = c == null ? string.Empty : c.TelNumber,
                    Website = c == null ? string.Empty : c.Website,
                    PostalCode = c == null ? string.Empty : c.PostalCode,
                    Remarks = c == null ? string.Empty : c.Remarks

                };

            query = query.OrderBy(c => c.Id);

            NewCustomerEntity result = query.SingleOrDefault();
            string Svalue = "";
            if (CustomerConstants.Status.TryGetValue(result.Status, out Svalue))
            {
                result.StatusValue = Svalue;
            }
            
            return result;
        }
        /// <summary>
        /// 通过code查找顾客
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public virtual CustomerEntity GetCustomerByCode(string code)
        {
            var query = from c in Context.CustomerEntities
                where c.CustomerCode == code
                select c;
            return query.SingleOrDefault();

        }

        /// <summary>
        /// 根据顾客名字或编号查询
        /// </summary>
        /// <param name="CustomerName"></param>
        /// <param name="CustomerCode"></param>
        /// <returns></returns>
        public virtual IList<NewCustomerEntity> GetCustomerByParameter(string CustomerName = null,
            string CustomerCode = null, int? skip = null, int? take = null)
        {
            if ((skip == null) ^ (take == null))
                throw new ArgumentException("Skip and take parameter must be provided together or left null.");

            var query = from c in Context.CustomerEntities
                join i in Context.IndustryEntities
                    on c.IndustryId equals i.Id into custindus
                from ci in custindus.DefaultIfEmpty()
                join e in Context.EmployeeEntities
                    on c.Employee equals e.Id into custemp
                from ce in custemp.DefaultIfEmpty()
                join co in Context.CountryEntities
                    on c.CountryId equals co.CountryId into custcount
                from cco in custcount.DefaultIfEmpty()
                select new NewCustomerEntity
                {
                    Id = c == null ? 0 : c.Id,
                    Address = c == null ? string.Empty : c.Address,
                    CountryId = c == null ? 0 : c.CountryId,
                    Country = cco == null ? string.Empty : cco.CountryName,
                    CustomerCode = c == null ? string.Empty : c.CustomerCode,
                    CustomerName = c == null ? string.Empty : c.CustomerName,
                    Email = c == null ? string.Empty : c.Email,
                    Employee = c == null ? 0 : c.Employee,
                    EmployeeName = ce == null ? string.Empty : ce.Name,
                    FaxNumber = c == null ? string.Empty : c.FaxNumber,
                    Status = c == null ? 0 : c.Status,
                    IndustryId = c == null ? 0 : c.IndustryId,
                    Industry = ci == null ? string.Empty : ci.Name,
                    TelNumber = c == null ? string.Empty : c.TelNumber,
                    Website = c == null ? string.Empty : c.Website,
                    PostalCode = c == null ? string.Empty : c.PostalCode,
                    Remarks = c == null ? string.Empty : c.Remarks

                };
            if (!string.IsNullOrWhiteSpace(CustomerName))
            {
                query = query.Where(c => c.CustomerName.Contains(CustomerName));
            }
            if (!string.IsNullOrWhiteSpace(CustomerCode))
            {
                query = query.Where(c => c.CustomerCode.Contains(CustomerCode));
            }
            query = query.OrderBy(c => c.Id);

            if (skip != null && take != null)
            {
                query = query.Skip(skip ?? 0).Take(take ?? int.MaxValue);
            }

            IList<NewCustomerEntity> result = query.ToList();

            foreach (var item in result)
            {
                string Svalue = "";
                if (CustomerConstants.Status.TryGetValue(item.Status, out Svalue))
                {
                    item.StatusValue = Svalue;
                }
            }

            return result;

        }

        /// <summary>
        /// 查询所有顾客
        /// </summary>
        /// <returns></returns>
        public virtual IList<NewCustomerEntity> GetCustomers(int? skip = null, int? take = null)
        {
            if ((skip == null) ^ (take == null))
                throw new ArgumentException("Skip and take parameter must be provided together or left null.");

            var query = from c in Context.CustomerEntities
                        join i in Context.IndustryEntities
                            on c.IndustryId equals i.Id into custindus
                        from ci in custindus.DefaultIfEmpty()
                        join e in Context.EmployeeEntities
                            on c.Employee equals e.Id into custemp
                        from ce in custemp.DefaultIfEmpty()
                        join co in Context.CountryEntities
                            on c.CountryId equals co.CountryId into custcount
                        from cco in custcount.DefaultIfEmpty()
                        select new NewCustomerEntity()
                        {
                            Id = c == null ? 0 : c.Id,
                            Address = c == null ? string.Empty : c.Address,
                            CountryId = c == null ? 0 : c.CountryId,
                            Country = cco == null ? string.Empty : cco.CountryName,
                            CustomerCode = c == null ? string.Empty : c.CustomerCode,
                            CustomerName = c == null ? string.Empty : c.CustomerName,
                            Email = c == null ? string.Empty : c.Email,
                            Employee = c == null ? 0 : c.Employee,
                            EmployeeName = ce == null ? string.Empty : ce.Name,
                            FaxNumber = c == null ? string.Empty : c.FaxNumber,
                            Status = c == null ? 0 : c.Status,
                            IndustryId = c == null ? 0 : c.IndustryId,
                            Industry = ci == null ? string.Empty : ci.Name,
                            TelNumber = c == null ? string.Empty : c.TelNumber,
                            Website = c == null ? string.Empty : c.Website,
                            PostalCode = c == null ? string.Empty : c.PostalCode,
                            Remarks = c == null ? string.Empty : c.Remarks

                        };

            query = query.OrderBy(c => c.Id);

            if (skip != null && take != null)
            {
                query = query.Skip(skip ?? 0).Take(take ?? int.MaxValue);
            }

            IList<NewCustomerEntity> result = query.ToList();

            foreach (var item in result)
            {
                string Svalue = "";
                if (CustomerConstants.Status.TryGetValue(item.Status, out Svalue))
                {
                    item.StatusValue = Svalue;
                }
            }

            return result;
        }
        /// <summary>
        /// 查询所有已激活顾客
        /// </summary>
        /// <returns></returns>
        public virtual IList<NewCustomerEntity> GetCustomerByStatus()
        {
            int StatusID = (int)CustomerConstants.StatuEnum.Active;

            var query = from c in Context.CustomerEntities
                        join i in Context.IndustryEntities
                            on c.IndustryId equals i.Id into custindus
                        from ci in custindus.DefaultIfEmpty()
                        join e in Context.EmployeeEntities
                            on c.Employee equals e.Id into custemp
                        from ce in custemp.DefaultIfEmpty()
                        join co in Context.CountryEntities
                            on c.CountryId equals co.CountryId into custcount
                        from cco in custcount.DefaultIfEmpty()
                        where c.Status==StatusID
                        select new NewCustomerEntity()
                        {
                            Id = c == null ? 0 : c.Id,
                            Address = c == null ? string.Empty : c.Address,
                            CountryId = c == null ? 0 : c.CountryId,
                            Country = cco == null ? string.Empty : cco.CountryName,
                            CustomerCode = c == null ? string.Empty : c.CustomerCode,
                            CustomerName = c == null ? string.Empty : c.CustomerName,
                            Email = c == null ? string.Empty : c.Email,
                            Employee = c == null ? 0 : c.Employee,
                            EmployeeName = ce == null ? string.Empty : ce.Name,
                            FaxNumber = c == null ? string.Empty : c.FaxNumber,
                            Status = c == null ? 0 : c.Status,
                            IndustryId = c == null ? 0 : c.IndustryId,
                            Industry = ci == null ? string.Empty : ci.Name,
                            TelNumber = c == null ? string.Empty : c.TelNumber,
                            Website = c == null ? string.Empty : c.Website,
                            PostalCode = c == null ? string.Empty : c.PostalCode,
                            Remarks = c == null ? string.Empty : c.Remarks

                        };

            query = query.OrderBy(c => c.Id);

            IList<NewCustomerEntity> result = query.ToList();

            foreach (var item in result)
            {
                string Svalue = "";
                if (CustomerConstants.Status.TryGetValue(item.Status, out Svalue))
                {
                    item.StatusValue = Svalue;
                }
            }

            return result;
        }
        /// <summary>
        /// 创建顾客
        /// </summary>
        /// <param name="Customer"></param>
        public virtual void CreateCustomer(CustomerEntity Customer)
        {
            Context.CustomerEntities.Add(Customer);
            Context.SaveChanges();
        }
        /// <summary>
        /// 修改顾客
        /// </summary>
        /// <param name="Customer"></param>
        public virtual void UpdateCustomer(CustomerEntity Customer)
        {
            Context.CustomerEntities.Attach(Customer);
            Context.Entry(Customer).State = System.Data.EntityState.Modified;
            Context.SaveChanges();
        }
        /// <summary>
        /// 删除顾客
        /// </summary>
        /// <param name="ID"></param>
        public virtual void DeleteCustomer(int ID)
        {
            string strSql = "DELETE FROM Customer WHERE Id = @Id ";
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(new SqlParameter("@Id", ID));
            Context.Database.ExecuteSqlCommand(strSql, param.ToArray());
        }
    }
}
